package websem.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import websem.WebsemProperties;
import websem.model.InfosTerm;
import websem.model.Paragraph;
import websem.model.Term;

public class DatabaseRequest {

	private Connection connection;
	
	private static final String SET_PARAGRAPH_WEIGHT = "UPDATE Paragraph P SET Weight=? WHERE P.XPath=? AND P.LabelDocument=?";
	
	/**
	 * Creating a databaseRequest object. Connect to the database.
	 */
	public DatabaseRequest() {
		try {
			this.connection = null;
			
			// Connect to database
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			WebsemProperties prop = new WebsemProperties();
			this.connection = DriverManager.getConnection(prop.getConnectionURL(), prop.getConnectionUser(), prop.getConnectionPassword());
			this.connection.setAutoCommit(false);
			
			// Test connection
			if (!this.connection.isClosed()) {
				System.out.println("--- Connection success.");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}	
	}
	
	/**
	 * Send a request to the database
	 * @param request
	 */
	public void sendRequest(String request) {
		
	}
	
	/**
	 * Get a complete term from database
	 * @param termLabel
	 * @return
	 */
	@SuppressWarnings("finally")
	public Term getTerm(String termLabel) {
//		System.out.println("Getting :: " + termLabel);
		Term term = null;
		
		try {
			// Get term from database
			String getTerm_sql = "SELECT IDTerm FROM Term WHERE Label = '" + termLabel + "'";
			PreparedStatement getTerm_statement = this.connection.prepareStatement(getTerm_sql);
			ResultSet term_result = getTerm_statement.executeQuery();
			
			if (!term_result.isBeforeFirst()) {
				return null;
			} else {
					term_result.next();
					term = new Term(termLabel);
					
					// Add term id
					term.setId(term_result.getInt("IDTerm"));
					
					// Get term contains from database to get paragraph that contains requested terms
					String getContains_sql = "SELECT * FROM Contain WHERE IDTerm=" + term.getId();
					PreparedStatement getContains_statement = this.connection.prepareStatement(getContains_sql);
					ResultSet contains_result = getContains_statement.executeQuery();
					
					// Prepare statement and variables for running contains result set
					// Paragraph values
					String getParagraph_sql = "SELECT IDParagraph, XPath, LabelDocument FROM Paragraph WHERE IDParagraph = ?";
					PreparedStatement getParagraph_statement = this.connection.prepareStatement(getParagraph_sql);
					int lastParagraphID = 0;
					Paragraph lastParagraph = null;
					
					// Positions values
					String getPositions_sql = "SELECT PositionValue FROM Position WHERE IDContain = ?";
					PreparedStatement getPositions_statement = this.connection.prepareStatement(getPositions_sql);
					
					while (contains_result.next()) {
						// Check if next containing paragraph is different from the last one
						// In that case, there is no need to do another query
						if (lastParagraphID != contains_result.getInt(3)) {
							lastParagraphID = contains_result.getInt(3);
							getParagraph_statement.setInt(1, lastParagraphID);
							
							// Get paragraph from current contain
							ResultSet paragraph_result = getParagraph_statement.executeQuery();
							paragraph_result.next();
							lastParagraph = new Paragraph(Paragraph.TYPE_PARAGRAPH, paragraph_result.getString(2), paragraph_result.getString(3));
							lastParagraph.setId(paragraph_result.getInt("IDParagraph"));
						}
						
						// Get position relative to contains
						getPositions_statement.setInt(1, contains_result.getInt(1));
						ResultSet position = getPositions_statement.executeQuery();
						position.next();
						
						// If paragraph doesn't exists into infos term, create a new InfosTerm object
						if (term.getInfos().get(lastParagraph) == null) {
							term.getInfos().put(lastParagraph, new InfosTerm());
						}
						
						// Add position
						InfosTerm infos = term.getInfos().get(lastParagraph);				
						infos.getPositions().add(position.getInt(1));
						infos.setWeight(contains_result.getDouble(3));
					}
				return term;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			return term;
		}
	}
	
	/** 
	 * Get all paragraphs from database
	 * @return
	 */
	@SuppressWarnings("finally")
	public ArrayList<Paragraph> getAllParagraphs() {
		ArrayList<Paragraph> paragraphs = new ArrayList<Paragraph>();
		
		try {
			// Prepare statements
			PreparedStatement get_paragraph_statement = this.connection.prepareStatement("SELECT IDParagraph, XPath, LabelDocument, Weight FROM Paragraph");
			ResultSet paragraphs_results = get_paragraph_statement.executeQuery();
			paragraphs_results.next();
			
			while (!paragraphs_results.isAfterLast()) {
				Paragraph p = new Paragraph(Paragraph.TYPE_PARAGRAPH, paragraphs_results.getString(2), paragraphs_results.getString(3));
				p.setId(paragraphs_results.getInt("IDParagraph"));
				p.setDocumentName(paragraphs_results.getString("LabelDocument"));
				p.setxPath(paragraphs_results.getString("XPath"));
				p.setWeight((double)(paragraphs_results.getInt("Weight")));
				paragraphs.add(p);
				paragraphs_results.next();
			}
			
			return paragraphs;
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			return paragraphs;
		}
	}

	/**
	 * Update database with paragraphs weights
	 * @param term the term
	 */
	public void addParagraphsWeights(Term term) {
		HashMap<Paragraph, InfosTerm> infos = term.getInfos();
		for (Paragraph p : infos.keySet()){
			try {
				PreparedStatement statement = this.connection.prepareStatement(DatabaseRequest.SET_PARAGRAPH_WEIGHT);
				statement.setInt(1, (int) Math.round(p.getWeight()));
				statement.setString(2, p.getxPath());
				statement.setString(3, p.getDocumentName());
				statement.execute();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * commit database.
	 */
	public void commit() {
		try {
			this.connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Set all paragraphs weights to 0.
	 */
	public void clearWeights() {
		try {
			PreparedStatement statement = this.connection.prepareStatement("UPDATE Paragraph SET Weight=0");
			statement.execute();
			this.connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
